package com.onlyxiahui.of.muc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class RoomChatDAO {
	private static final Logger log = LoggerFactory.getLogger(RoomChatDAO.class);

	/**
	 * 查询离线消息
	 * @param roomId
	 * @param afterTime
	 * @return
	 */
	public static List<Map<String, String>> getRoomOfflineMessageList(long roomId, String afterTime) {

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		StringBuilder sql = new StringBuilder();

		sql.append(" select");
		sql.append("	roomID,");
		sql.append("	messageID,");
		sql.append("	sender,");
		sql.append("	nickname,");
		sql.append("	logTime,");
		sql.append("	`subject`,");
		sql.append("	body,");
		sql.append("	stanza");
		sql.append(" from");
		sql.append("	ofmucconversationlog ");
		sql.append(" where ");
		sql.append("	roomID = ? ");
		sql.append(" and logTime >= ? ");
		sql.append(" order by logTime ");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Map<String, String> map = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, roomId);
			statement.setString(2, afterTime);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				map = new HashMap<String, String>();
				map.put("roomId", resultSet.getString(1));
				map.put("messageId", resultSet.getString(2));
				map.put("sender", resultSet.getString(3));
				map.put("nickname", resultSet.getString(4));
				map.put("logTime", resultSet.getString(5));
				map.put("subject", resultSet.getString(6));
				map.put("body", resultSet.getString(7));
				map.put("stanza", resultSet.getString(8));
				list.add(map);
			}
		} catch (Exception e) {
			log.error("", e);
			// e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}

	
	
	/**
	 * 消息总数
	 * @param roomId
	 * @return
	 */
	public static int getRoomMessageCount(long roomId) {
		int count = 0;

		StringBuilder sql = new StringBuilder();
		sql.append(" select");
		sql.append(" count(1) ");
		sql.append(" from");
		sql.append("	ofmucconversationlog ");
		sql.append(" where ");
		sql.append("	roomID = ? ");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, roomId);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException sqle) {
			log.error(sqle.getMessage(), sqle);
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return count;
	}

	/**
	 * 聊天室聊天记录
	 * @param roomId
	 * @param start
	 * @param count
	 * @return
	 */
	public static List<Map<String, String>> getRoomMessageList(
			long roomId,
			int start,
			int count) {

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		StringBuilder sql = new StringBuilder();

		sql.append(" select");
		sql.append("	roomID,");
		sql.append("	messageID,");
		sql.append("	sender,");
		sql.append("	nickname,");
		sql.append("	logTime,");
		sql.append("	`subject`,");
		sql.append("	body,");
		sql.append("	stanza");
		sql.append(" from");
		sql.append("	ofmucconversationlog ");
		sql.append(" where ");
		sql.append("	roomID = ? ");
		sql.append(" order by logTime ");
		sql.append(" desc limit ?,?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Map<String, String> map = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setLong(1, roomId);
			statement.setInt(2, start);
			statement.setInt(3, count);

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				map = new HashMap<String, String>();
				map.put("roomId", resultSet.getString(1));
				map.put("messageId", resultSet.getString(2));
				map.put("sender", resultSet.getString(3));
				map.put("nickname", resultSet.getString(4));
				map.put("logTime", resultSet.getString(5));
				map.put("subject", resultSet.getString(6));
				map.put("body", resultSet.getString(7));
				map.put("stanza", resultSet.getString(8));
				list.add(map);
			}
		} catch (Exception e) {
			log.error("", e);
			// e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	private static final long roomId = -2;
	private static final String subject = "";
	private static final String ADD_CONVERSATION_LOG = "INSERT INTO ofMucConversationLog (roomID,messageID,sender,nickname,logTime,subject,body,stanza) "
			+ "SELECT ?,COUNT(*),?,?,?,?,?,? FROM ofMucConversationLog";

	public static boolean saveRoomChatLog(String sendJid, String roomJid, String body, Date date, String xml) {

		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(ADD_CONVERSATION_LOG);
			pstmt.setLong(1, roomId);
			pstmt.setString(2, sendJid);
			pstmt.setString(3, roomJid);
			pstmt.setString(4, StringUtils.dateToMillis(date));
			pstmt.setString(5, subject);
			pstmt.setString(6, body);
			pstmt.setString(7, xml);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException sqle) {
			log.error("Error saving conversation log entry", sqle);
			return false;
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}
	
	/**
	 * 消息总数
	 * @param roomJid
	 * @return
	 */
	public static int getRoomMessageCount(String roomJid) {
		int count = 0;

		StringBuilder sql = new StringBuilder();
		sql.append(" select");
		sql.append(" count(1) ");
		sql.append(" from");
		sql.append("	ofmucconversationlog ");
		sql.append(" where ");
		sql.append(" roomID=-2 and nickname = ? ");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, roomJid);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException sqle) {
			log.error(sqle.getMessage(), sqle);
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return count;
	}

	/**
	 * 聊天室聊天记录
	 * @param roomJid
	 * @param start
	 * @param count
	 * @return
	 */
	public static List<Map<String, String>> getRoomMessageList(
			String roomJid,
			int start,
			int count) {

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		StringBuilder sql = new StringBuilder();

		sql.append(" select");
		sql.append("	roomID,");
		sql.append("	messageID,");
		sql.append("	sender,");
		sql.append("	nickname,");
		sql.append("	logTime,");
		sql.append("	`subject`,");
		sql.append("	body,");
		sql.append("	stanza");
		sql.append(" from");
		sql.append("	ofmucconversationlog ");
		sql.append(" where ");
		sql.append(" roomID=-2 and nickname = ? ");
		sql.append(" order by logTime ");
		sql.append(" desc limit ?,?");

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Map<String, String> map = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(sql.toString());
			statement.setString(1, roomJid);
			statement.setInt(2, start);
			statement.setInt(3, count);

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				map = new HashMap<String, String>();
				map.put("roomId", resultSet.getString(1));
				map.put("messageId", resultSet.getString(2));
				map.put("sendJid", resultSet.getString(3));
				map.put("receiveJid", resultSet.getString(4));
				map.put("logTime", resultSet.getString(5));
				map.put("subject", resultSet.getString(6));
				map.put("body", resultSet.getString(7));
				map.put("stanza", resultSet.getString(8));
				list.add(map);
			}
		} catch (Exception e) {
			log.error("", e);
			// e1.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return list;
	}
}
